Computer database systems manage the storage and retrieval of data in a database. A database comprises a set of tables of data along with information about relations between the tables. Tables represent relations over the data. Each table comprises a set of records of data stored in one or more data fields. The records of a table are also referred to as rows, and the data fields of records in a table are also referred to as columns.
A database server processes data manipulation statements or queries, for example, to retrieve, insert, delete, and update data in a database. Queries are defined by a query language supported by the database system. To enhance performance in processing queries, database servers use indexes to help access data in a database more efficiently. Although all possible indexes for a database can be constructed, the number of possible indexes over a database can be very large and can significantly increase the memory requirements, including storage requirements, for the database. Also, many of the possible indexes may provide no or minimal performance advantage considering the data in the database, the organization of the data in the database, and the usage of the database as represented by a workload of queries executed against the database. Typical database systems therefore use only a subset of possible indexes for accessing databases.
A physical database design tool or database administrator typically selects a set of indexes for use by a database system from among possible combinations of indexes for a database. The effectiveness of any set of indexes depends, for example, on the data in the database, on the query optimizer accessing the database, and on the workload of queries to be executed against the database. As a database system evolves through database updates and modifications and through changes in workloads of queries, for example, new sets of indexes should be considered and selected to help maintain a desired level of performance of the database system.
Because the space of possible indexes over a database can be very large, however, searching among such indexes can incur substantial cost in time and memory. The problem of selecting a set of indexes can be further compounded by the presence of multi-column indexes and by the innovative ways some query processors use indexes, such as index intersection and indexed-only access for example. Although a physical database design tool may be used to automate the selection of a set of indexes in light of these concerns, the search algorithm of the design tool may not address specific concerns of the database administrator such as, for example: (1) Which queries executed in the last three days will slow down because of projected changes in the database? or (2) Which queries will benefit from the index(es) that the design tool is proposing to add and to what extent?